{*******************************************************************************
* The contents of this file are used with permission, subject to the Mozilla   *
* Public License Version 1.1 (the "License"); you may not use this file except *
* in compliance with the License. You may obtain a copy of the License at      *
* http://www.mozilla.org/MPL/                                                  *
*                                                                              *
* Software distributed under the License is distributed on an "AS IS" basis,   *
* WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for *
* the specific language governing rights and limitations under the License.    *
*                                                                              *
* The Original Code is distributed as part of the "mediate" product and is     *
* Copyright (C) @home media limited. All Rights Reserved.                      *
*                                                                              *
* Email: support@athomemedia.co.uk                                             *
* Web:   http://www.athomemedia.co.uk                                          *
*******************************************************************************}

unit CameraSchema;

(*
  Camera schema explanatory notes
  ==============================

  This schema forms a list of known camera locations with a single Camera table.

  Camera
  ------

  This table has one row per camera (a speed camera, red light camera or
  mobile camera site) and columns for camera locationmedia name (title), artist, genre, file url, year and size.

  - Id is a system generated integer acting as primary key.
  - Country is a string containing a country code (e.g. "GB").
  - Lattitude is a floating point lattitude value.
  - Longitude is a floating point longitude value.
  - Speed is an integer value denoting an enforced speed limit in the default
    units for the corresponding country (i.e. Mph or Kph)
  - CameraType is an integer value denoting the type of camera site
  - Heading is an integer value denoting the camera orientation
  - Reversible is a boolean value indicating whether the camera orientation may
    be reversed or is always active in two directions
  - Description is a string containing a short description of the camera site

*)

interface

uses DBSchema;

const
  CAMERA_DB_FILENAME = 'cameras.db3';

  // Define common field positions used in queries below. We should adhere
  // to this standard wherever possible so we don't need lots of field defs.
//  FIELD_ID = 0;
//  FIELD_VAL = 1;
//  FIELD_URL = 5;

type
  // Enumerated type to determine camera type
  TAHMCameraType = (ctFixed, ctAverage, ctMobile, ctSignal);

  TAHMCameraSchema = class(TAHMDBSchema)
  private
    FPrepared: Boolean;
    FInsertCamera: TAHMDBQuery;
  protected
  public
    function CheckSchema: Boolean; override;
    procedure BuildIndexes;
    procedure PopulateTestData;
    procedure PrepareInsertQueries;
    procedure ReleaseInsertQueries;
    procedure InsertCamera(Id: Integer; const Country, Description: String;
                           Lattitude, Longitude: Double; Speed, Heading: Integer;
                           CameraType: TAHMCameraType; Reversible: Boolean);
    function ListCameras(const Country: String; MinLattitude, MaxLattitude,
                         MinLongitude, MaxLongitude: Double): TAHMDBResult;
  end;

implementation

uses SysUtils;

const
  // Table DML
  // =========

  // Camera sites
  TBL_CAMERA = 'Camera';
  SQL_BUILD_CAMERA_TABLE = 'CREATE TABLE Camera (' +
                           '[Id] INTEGER PRIMARY KEY, ' +
                           '[Country] CHAR, ' +
                           '[Lattitude] FLOAT, ' +
                           '[Longitude] FLOAT, ' +
                           '[Speed] INTEGER, ' +
                           '[CameraType] INTEGER, ' +
                           '[Heading] INTEGER, ' +
                           '[Reversible] BOOLEAN DEFAULT 0, ' +
                           '[Description] CHAR)';
  SQL_BUILD_CAMERA_INDEX1 = 'CREATE INDEX CamCountry ON [Camera]([Country])';
  SQL_BUILD_CAMERA_INDEX2 = 'CREATE INDEX CamLattitude ON [Camera]([Lattitude])';
  SQL_BUILD_CAMERA_INDEX3 = 'CREATE INDEX CamLongitude ON [Camera]([Longitude])';

  // Insert statements
  SQL_INSERT_CAMERA = 'INSERT INTO Camera(Id, Country, Lattitude, Longitude, ' +
                      'Speed, CameraType, Heading, Reversible, Description) ' +
                      'VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)';

  // Queries
  // =======

  // Retrieve nearby cameras for a given country and min/max lattitude/longitude
  SQL_GET_NEARBY_CAMERAS = 'SELECT Id, Lattitude, Longitude, Speed, CameraType, ' +
                           'Heading, Reversible, Description ' +
                           'FROM Camera WHERE Country=%0:s AND Id IN (' +
                           'SELECT Id FROM Camera WHERE Lattitude BETWEEN %1:f AND %2:f ' +
                           'INTERSECT ' +
                           'SELECT Id FROM Camera WHERE Longitude BETWEEN %3:f AND %4:f)';


// TAHMCameraSchema

function TAHMCameraSchema.CheckSchema: Boolean;
begin
  inherited CheckSchema;

  if not FDB.TableExists(TBL_CAMERA) then
    FDB.ExecSQL(SQL_BUILD_CAMERA_TABLE);

  Result := True;
end;

procedure TAHMCameraSchema.BuildIndexes;
begin
  FDB.ExecSQL(SQL_BUILD_CAMERA_INDEX1);
  FDB.ExecSQL(SQL_BUILD_CAMERA_INDEX2);
  FDB.ExecSQL(SQL_BUILD_CAMERA_INDEX3);
end;

procedure TAHMCameraSchema.PopulateTestData;
const
  // Bounding box for NE Wales to NW London
  TEST_COUNTRY = 'UK';
  MAX_LATTITUDE = 53.291489065300226;
  MIN_LONGITUDE = -3.44970703125;
  MIN_LATTITUDE = 51.73383267274113;
  MAX_LONGITUDE = -0.1318359375;
  LATT_RANGE = MAX_LATTITUDE - MIN_LATTITUDE;
  LONG_RANGE = MAX_LONGITUDE - MIN_LONGITUDE;
var
  i: Integer;
  Reversible: Boolean;
begin
  FDB.Synchronised := False;
  FDB.BeginTransaction;
  PrepareInsertQueries;
  try
    Reversible := False;

    // Populate some random camera sites
    for i := 1 to 1000 do
    begin
      InsertCamera(i, TEST_COUNTRY, 'This is camera #' + IntToStr(i),
                   MIN_LATTITUDE + (Random * LATT_RANGE),
                   MIN_LONGITUDE + (Random * LONG_RANGE),
                   30 + (Random(5) * 10), Random(360),
                   TAHMCameraType(Random(4)), Reversible);
      Reversible := not Reversible;
    end;
  finally
    ReleaseInsertQueries;
    FDB.Commit;
  end;
end;

procedure TAHMCameraSchema.PrepareInsertQueries;
begin
  if not FPrepared then
  begin
    FInsertCamera := FDB.PrepareSQL(SQL_INSERT_CAMERA);
    FPrepared := True;
  end;
end;

procedure TAHMCameraSchema.ReleaseInsertQueries;
begin
  if FPrepared then
  begin
    FDB.ReleaseSQL(FInsertCamera);
    FPrepared := False;
  end;
end;

procedure TAHMCameraSchema.InsertCamera(Id: Integer; const Country, Description: String;
                                        Lattitude, Longitude: Double; Speed, Heading: Integer;
                                        CameraType: TAHMCameraType; Reversible: Boolean);
begin
  FDB.BindSQL(FInsertCamera, 1, Id);
  FDB.BindSQL(FInsertCamera, 2, Country);
  FDB.BindSQL(FInsertCamera, 3, Lattitude);
  FDB.BindSQL(FInsertCamera, 4, Longitude);
  FDB.BindSQL(FInsertCamera, 5, Speed);
  FDB.BindSQL(FInsertCamera, 6, Ord(CameraType));
  FDB.BindSQL(FInsertCamera, 7, Heading);
  FDB.BindSQL(FInsertCamera, 8, Reversible);
  FDB.BindSQL(FInsertCamera, 9, Description);
  FDB.ExecSQL(FInsertCamera);
end;

function TAHMCameraSchema.ListCameras(const Country: String; MinLattitude, MaxLattitude,
                                      MinLongitude, MaxLongitude: Double): TAHMDBResult;
var
  SQL: String;
begin
  try
    // Format SQL expression
    SQL := Format(SQL_GET_NEARBY_CAMERAS, [Country, MinLattitude,
                         MaxLattitude, MinLongitude, MaxLongitude]);

    // Get all cameras for specified country and bounding box
    Result := FDB.GetTable(SQL);
  except
    // Catch format exceptions
    Result := nil;
  end;
end;


end.
